package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import util.JDBCUtil;
import domain.ForumTopic;
import ex.DatabaseException;

public class ForumTopicDAO {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	
	private static final String SELECT_FORUM_TOPIC_BY_TID="select * from forum_topic where tid= ?";
	private static final String SELECT_FORUM_TOPIC_BY_CID="select * from forum_topic where cid= ?";
	private static final String INSERT_FORUM_TOPIC_BY_CID="insert into forum_topic (cid,title,uid,date,flag) values (?,?,?,?,?)";
	private static final String DELETE_FORUM_TOPIC_BY_TID="delete from forum_topic where tid = ?";
	private static final String UPDATE_FORUM_TOPIC="update forum_topic set title = ?, flag = flag + 1 where tid = ?";
	
	//Selection of topics from ForumTopic table
	public ForumTopic selectForumTopicByTid(long tid) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		ForumTopic ft = null;
		try{
			stmt = conn.prepareStatement(SELECT_FORUM_TOPIC_BY_TID);
			stmt.setLong(1, tid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				ft = new ForumTopic(rs.getLong("tid"), rs.getLong("cid"), rs.getString("title"), rs.getLong("uid"), rs.getTimestamp("date"), rs.getInt("flag"));
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);		
		}catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving forum topic by tid! "+ e.getMessage());
		}	
		return ft;
	}
	
	//Selection of topics from ForumTopic table using Category ID
	public LinkedList<ForumTopic> selectForumTopicByCid(long cid) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		LinkedList<ForumTopic> list = new LinkedList<ForumTopic>();
		ForumTopic ft = null;
		try{
			stmt = conn.prepareStatement(SELECT_FORUM_TOPIC_BY_CID);
			stmt.setLong(1, cid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				ft = new ForumTopic(rs.getLong("tid"), rs.getLong("cid"), rs.getString("title"), rs.getLong("uid"), rs.getTimestamp("date"), rs.getInt("flag"));
				list.add(ft);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);		
		}catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving forum topic by cid! "+ e.getMessage());
		}
		return list;
	}
	
	//Insertion of a new Topic to the database
	public void insertForumTopicByCid(ForumTopic ft) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(INSERT_FORUM_TOPIC_BY_CID);
			stmt.setLong(1, ft.getCid());
			stmt.setString(2, ft.getTitle());
			stmt.setLong(3, ft.getUid());
			stmt.setTimestamp(4, ft.getDate());
			stmt.setInt(5, ft.getFlag());
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with inserting forum topic! "	+ e.getMessage());
		}				
	}
	
	//Removal of a Topic from the database
	public void deleteForumTopic(long tid) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(DELETE_FORUM_TOPIC_BY_TID);
			stmt.setLong(1, tid);
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with deleting forum topic! "	+ e.getMessage());
		}				
	}
	
	//Editing the ForumTopic fields
	public void updateForumTopic (ForumTopic ft) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(UPDATE_FORUM_TOPIC);
			stmt.setString(1, ft.getTitle());
			stmt.setLong(2, ft.getTid());
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with updating forum topic! "	+ e.getMessage());
		}				
	}
	

}
